package com.fr.data;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fr.script.Calculator;
import com.fr.third.org.apache.commons.io.IOUtils;
import com.fr.third.org.apache.commons.lang3.StringUtils;
import com.fr.third.org.apache.commons.lang3.time.DateFormatUtils;

public class DWTotalSubmitJob extends TotalSubmitJob {
	
	private static final Logger LOG = LoggerFactory.getLogger(DWTotalSubmitJob.class);
	
	private static final String mainTableName = "mainTable";
	private static final String historyTableName = "historyTable";
	private static final String mainColumnName = "mainColumn";

	private static final String addTimeColumn = "ADD_TIME";
	private static final String deleteTimeColumn = "DELETE_TIME";

	private static final String DATEFORMAT = "yyyy-MM";
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	@Override
	protected void doTotalJob(Data data, Calculator calculator) throws Exception {
		Connection conn = null;
		boolean closealbe = false;
		try {
			System.out.println(1);
			conn = calculator.getConnection("hs05");
			if (conn == null) {
				conn = this.getConnection();
				closealbe = true;
			}
			System.out.println(2);
			String mainTable = null;
			String historyTable = null;
			String mainColumn = null;
			int mainIndex = -1;
			for (int i = 0; i < data.getColumnCount(); i++) {
				if (StringUtils.equals(mainColumnName, data.getColumnName(i))) {
					mainColumn = this.getStringValue(data.getValueAt(0, i));
				}
				if (StringUtils.equals(mainTableName, data.getColumnName(i))) {
					mainTable = this.getStringValue(data.getValueAt(0, i));
				}
	
				if (StringUtils.equals(historyTableName, data.getColumnName(i))) {
					historyTable = this.getStringValue(data.getValueAt(0, i));
				}
			}
			for (int i = 0; i < data.getColumnCount(); i++) {
				if (StringUtils.equals(mainColumn, data.getColumnName(i))) {
					mainIndex = i;
					break;
				}
			}
	
			List<Map<String, String>> datas = this.getAllData(conn, mainTable, mainColumn);
	
			String mainValue = null;
			LOG.error("总条数：" + data.getRowCount());
			for (int index = 0; index < data.getRowCount(); index++) {
				mainValue = this.getStringValue(data.getValueAt(index, mainIndex));
	
				List<String> columns = new ArrayList<String>();
				List<String> values = new ArrayList<String>();
				List<String> placeholders = new ArrayList<String>();
				for (int j = 0; j < data.getColumnCount(); j++) {
					if (StringUtils.equals(mainColumnName, data.getColumnName(j)) || StringUtils.equals(mainTableName, data.getColumnName(j))
							|| StringUtils.equals(historyTableName, data.getColumnName(j))) {
						continue;
					}
					columns.add(data.getColumnName(j));
					values.add(this.getStringValue(data.getValueAt(index, j)));
					placeholders.add("?");
				}
	
				Map<String, String> rowData = this.getData(datas, mainColumn, mainValue);
				// 新增
				if (rowData == null) {
					// 插入数据到mainTable
					columns.add(addTimeColumn);
					values.add(DateFormatUtils.format(Calendar.getInstance(), DATEFORMAT));
					placeholders.add("?");
					this.insert(conn, mainTable, columns, values, placeholders);
	
					// 编辑
				} else {
					datas.remove(rowData);
					this.update(conn, mainTable, columns, values, mainColumn, mainValue);
				}
			}
	
			// 处理删除
			// 将数据移入到historyTable
			List<String> historyValues = new ArrayList<String>();
			List<String> historyPlaceholders = new ArrayList<String>();
			for (Map<String, String> row : datas) {
				row.put(deleteTimeColumn, DateFormatUtils.format(Calendar.getInstance(), DATEFORMAT));
				this.insertHistory(conn, historyTable, row);
				historyValues.add(row.get(mainColumn));
				historyPlaceholders.add("?");
			}
			// 删除数据mainTable
			if (!historyValues.isEmpty()) {
				this.delete(conn, mainTable, mainColumn, historyValues, historyPlaceholders);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (closealbe && conn != null) {
				conn.close();
			}
		}
	}

	private Object getValue(Object value) {
		if (value instanceof JobValue) {
			JobValue ce = (JobValue) value;
			return ce.getValue();
		}
		return value;
	}

	private String getStringValue(Object value) {
		return String.valueOf(this.getValue(value));
	}

	private void insertHistory(Connection conn, String historyTable, Map<String, String> row) throws Exception {
		List<String> columns = new ArrayList<String>();
		List<String> values = new ArrayList<String>();
		List<String> placeholders = new ArrayList<String>();
		for (String key : row.keySet()) {
			columns.add(key);
			values.add(row.get(key));
			placeholders.add("?");
		}
		this.insert(conn, historyTable, columns, values, placeholders);
	}

	private void insert(Connection conn, String mainTable, List<String> columns, List<String> values, List<String> placeholders) throws Exception {
		PreparedStatement ps = null;
		try {
			String sql = "insert into " + mainTable + " ({0}) values ({1}) ";
			sql = MessageFormat.format(sql, StringUtils.join(columns, ","), StringUtils.join(placeholders, ","));
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= values.size(); i++) {
				ps.setString(i, values.get(i - 1));
			}
			ps.execute();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private void update(Connection conn, String mainTable, List<String> columns, List<String> values, String mainColumn, String mainValue)
			throws Exception {
		columns.add(addTimeColumn);
		values.add(DateFormatUtils.format(Calendar.getInstance(), DATEFORMAT));
		String sql = " update " + mainTable + " set {0} where " + mainColumn + "=?";

		List<String> setValues = new ArrayList<String>();
		for (String column : columns) {
			setValues.add(column + "=?");
		}

		sql = MessageFormat.format(sql, StringUtils.join(setValues, ","));
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= values.size(); i++) {
				ps.setString(i, values.get(i - 1));
			}
			ps.setString(values.size() + 1, mainValue);
			ps.execute();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private void delete(Connection conn, String mainTable, String mainColumn, List<String> mainValues, List<String> placeholders) throws Exception {
		PreparedStatement ps = null;
		try {
			String sql = "delete from " + mainTable + " where " + mainColumn + " in ({0})";
			sql = MessageFormat.format(sql, StringUtils.join(placeholders, ","));
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= mainValues.size(); i++) {
				ps.setString(i, mainValues.get(i - 1));
			}
			ps.execute();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public List<Map<String, String>> getAllData(Connection conn, String mainTable, String mainColumn) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "select * from " + mainTable;
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			List<Map<String, String>> result = new ArrayList<Map<String, String>>();
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				for (int i = 1; i <= columnCount; i++) {
					String value = rs.getString(i);
					String column = metaData.getColumnLabel(i);
					map.put(column, value);
				}
				result.add(map);
			}
			return result;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private Map<String, String> getData(List<Map<String, String>> datas, String mainColumn, String mainValue) throws Exception {
		for (Map<String, String> data : datas) {
			String value = data.get(mainColumn);
			if (StringUtils.equals(value, mainValue)) {
				return data;
			}
		}
		return null;
	}

	protected Connection getConnection() throws Exception {
		InputStream in = null;
		Properties properties = new Properties();
		try {
			in = this.getClass().getResourceAsStream("classpath:cadb-config.properties");
			properties.load(in);
		} finally {
			IOUtils.closeQuietly(in);
		}
		String driverClass = properties.getProperty("cadb.jdbc.driver");
		String url = properties.getProperty("cadb.jdbc.url");
		String username = properties.getProperty("cadb.jdbc.username");
		String password = properties.getProperty("cadb.jdbc.password");
		Class.forName(driverClass);
		return DriverManager.getConnection(url, username, password);
	}
}
